import ctypes
ctypes.windll.user32.ShowWindow(ctypes.windll.kernel32.GetConsoleWindow(), 0)
#上面两行确保批处理运行时不显示CMD窗口
from tkinter import *
from tkinter import ttk
import tkinter.messagebox
import tkinter as tk
from tkinter import Label
from turtle import color
import subprocess
import ast
import sys
import pymysql
import openpyxl as vb
from openpyxl.styles import PatternFill 
import datetime
import time
import copy
import getpass
import math
import ttkbootstrap as ttk
from email.mime.text import MIMEText
from email.header import Header
from email.mime.multipart import MIMEMultipart
from email.mime.image import MIMEImage
from zeep import Client
import os
import smtplib
import msvcrt
import pyttsx3 as pts



def windows(): 
    todaycolumn=datetime.datetime.now().strftime('%Y-%m-%d') 

    win =ttk.Window(title=str(version)+'   Released on '+release_date)    
    s=ttk.Style()
    s.theme_use('solar') 

    win.geometry('615x330')  #宽X高
    win.resizable(width=False,height=False)
    #.建立mysql的连接
    conn = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',
            password='123456',port=3306,charset='utf8')
    # 获得游标
    cursor = conn.cursor()  

    cursor.execute('''select max(version) from version_exam group by sw_name''')
    versionnumber=str(cursor.fetchone()[0])
    # print(type(versionnumber))
    # print(type(version[1:]))
    if versionnumber!=version[1:]:
        tkinter.messagebox.showerror(title='Hi', message='请获取软件更新版本 !')
        win.destroy()
    #从数据库取出不需要特殊电脑的考试科目清单
    special_subjects=[]
    cursor.execute('''select exam_name from settings where specialcomputer='N' ''')
    specials=cursor.fetchall()
    for i in specials:
        special_subjects.append(i[0])
    # print(special_subjects)

    
    lb0 = ttk.Label(win, text = "TCC Reliability 测试能力考核系统 ", font = "tahoma 15 normal")
    lb0.place(x=71,y=20)
    lb1 = ttk.Label(win, text = "科目选择", font = "tahoma 11 normal",foreground='yellow')
    lb1.place(x=83,y=85)
 
    lbx = ttk.Label(win, justify="left",text ='TCC Reliability Exam System/Initial released in Apr. 2023' , font = "tahoma 6 normal",foreground='white')
    lbx.place(x=160,y=300)

    
    def show_productlist(*args):
        print("Event: ComboboxSelected")
        print(cbx_1.get())

    productlist=['']   

    # 移除 ONLY_FULL_GROUP_BY（保留其他模式，全局变量设置）
    cursor.execute("SET GLOBAL sql_mode = (SELECT REPLACE(@@GLOBAL.sql_mode, 'ONLY_FULL_GROUP_BY', ''))")
 
    cursor.execute('''select test_name from examcontent where backup1='in-use' group by test_name order by test_name ASC''')
    # cursor.execute('''select product from ota_infor where product like concat('%',{},'%') group by product '''.format(input))
    products = cursor.fetchall()
    for i in products:
        productlist.append(i[0])

   
    chinesenamelist=['李华']        
    cursor.execute('''select name from collection''')
    # cursor.execute('''select product from ota_infor where product like concat('%',{},'%') group by product '''.format(input))
    chinesenames = cursor.fetchall()
    for i in chinesenames:
        chinesenamelist.append(i[0])
    # print(chinesenamelist)


    def show_select():        
        print(value.get())
        input=value.get()

        print('productlist0:',productlist)
        
        
        if input not in productlist and input !='':                      
            productlist.clear()
            print('productlist1:',productlist)            
            cursor.execute('''select test_name from examcontent where test_name like '{}%' and backup1='in-use' group by test_name'''.format(input))
            products = cursor.fetchall()
            for i in products:
                productlist.append(i[0])
            print('productlist2:',productlist)
            
        else:
            productlist.clear()
            cursor.execute('''select test_name from examcontent where backup1='in-use' group by test_name order by test_name ASC''')
            products = cursor.fetchall()
            for i in products:
                productlist.append(i[0])
        cbx_1["values"] = productlist

     
    value = StringVar()
    cbx_1 = ttk.Combobox(win, textvariable = value, postcommand = show_select,bootstyle="danger")
    cbx_1.place(x=80,y=115,width = 270,height=38)
    
    cbx_1.configure(state = "normal")
    
    #add data to combobox
    cbx_1["values"] = productlist
    #set the initial data [index =2] to shows up when win generated
    cbx_1.current(0)
    
    #bind a event
    cbx_1.bind("<<ComboboxSelected>>", show_productlist)
    
    #======================================================================================================
    #create a Label
    lb2 = ttk.Label(win, text = "输入学员姓名：", font = "tahoma 11 normal",foreground='yellow')
    lb2.place(x=360,y=85)

    e=ttk.Entry(win, show=None,bootstyle="danger")  #show='*' 、show='1' 输什么都显示1
    e.place(x=360,y=115,width = 150,height=38)

 


    
    def start_exam():  
         
        def select_and_display():    
            passwords=entry_days.get()
                       
             
            conn = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',password='123456',port=3306,charset='utf8')
            # 获得游标
            cursor = conn.cursor()
            cursor.execute('''SELECT * FROM settings where exam_name='all' ''')
            settinginforsall=cursor.fetchone()      
            keys=settinginforsall[5]  
            print(passwords,keys)

            if passwords==keys:

                #.获取当前日期      
                todaycolumn=datetime.datetime.now().strftime('%Y-%m-%d')  
                three_days_ago=(datetime.datetime.now()+datetime.timedelta(days=-2)).strftime('%Y-%m-%d')  #2天之前
                print(three_days_ago)


                #获取选择的科目名称
                test_seletion = cbx_1.get()
                # special_subjects=['RTTP小测验-1']
                # print(special_subjects)
                #获取用户名
                global user_name
                user_name = getpass.getuser()  
                #待获取名单
                global namelist
                namelist=['lijun24','nizhao2','Lijun24','wangjin24','lizh48','lijun241','lixin34','huangyl','W11743-PF50P6SV','w11743','lijun24_2025']    
 
                if user_name in namelist or test_seletion in special_subjects:   

                    def next():  # 下一题
                        global number
                        number += 1
                    
                        if number + 1 > rows:  # 说明到最后一条记录的后面
                            number = 0  # 回到第1条记录
                        display()
                    
                    
                    def previous():  # 上一题
                        global number
                        number -= 1
                    
                        if number == -1:  # 说明到了第1条记录的前面
                            number = rows-1  # 回到最后1条记录
                        display()
                        
                    
                    def display():  # 界面题目，答案等显示
                        global number,id, selected  # img1不定义为全局变量，图像可能无法显示
                        id = str(number+1)+".　"  # 序号
                    
                        global db_id,question_id

                        #.建立mysql的连接
                        conn = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',
                                password='123456',port=3306,charset='utf8')
                        # 获得游标
                        cursor = conn.cursor()              

                        #从examrecord中选出刚刚添加的这套题目
                        cursor.execute('''select * from examrecord where name=%s and exam_sequence=%s''',(name,exam_sequence))
                        selectedrows1=cursor.fetchall()

                        db_id=selectedrows1[number][0]
                        question=selectedrows1[number][3]
                        selectA=selectedrows1[number][4]
                        selectB=selectedrows1[number][5]
                        selectC=selectedrows1[number][6]
                        selectD=selectedrows1[number][7]
                        answer=selectedrows1[number][10]
                        selected=selectedrows1[number][11]
                        question_id=selectedrows1[number][17]  #为了上报问题
           
                        
 

                        if selectC=='':
                            selectC='----'
                        if selectD=='':
                            selectD='----'   
                    
                        la1.config(text=id+question)  # 题目前面加序号
                    
                        ra1.config(text='A.　'+selectA)  # 答案1加上A.
                        ra2.config(text='B.　'+selectB)  # 答案1加上B.
                        ra3.config(text='C.　'+selectC)  # 答案1加上C.
                        ra4.config(text='D.　'+selectD)  # 答案1加上D.
                    
                    
                        if selected is None:        
                            var.set(0)
                        elif selected=='A':
                            var.set(1)
                        elif selected=='B':
                            var.set(2)
                        elif selected=='C':
                            var.set(3)
                        elif selected=='D':
                            var.set(4)


                    def save_selection():  # 点击选项，存入DB
                    
                        if var.get()==1:
                            selected='A'
                        if var.get()==2:
                            selected='B'
                        if var.get()==3:
                            selected='C'
                        if var.get()==4:
                            selected='D'
                        
                        print('题目编号(examrecord):',db_id)   #打印一下现在做的题目的数据库内编号（examrecord), 通过编号进行答案存储
 
                        conn = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',
                                password='123456',port=3306,charset='utf8')
                        # 获得游标
                        cursor = conn.cursor()          

                        cursor.execute('''update examrecord set selected=%s where id=%s''',(selected,db_id))
                        conn.commit()
                    
                
                    def auto_email1(student,subject,score,question_number,fail_number,duration,fail_questions):
                        cursor.execute('''select mailbox from collection where name=%s''',student)
                        mail_id=cursor.fetchone()
                        mailaddress=mail_id[0]+'@lenovo.com'
                

                        from_addr='tcc_reliability@motorola.com'
                        
                        to_addr=['lizh48@lenovo.com','nizhao2@lenovo.com','wangjin24@lenovo.com','pengch2@lenovo.com']                       
                        # to_addr=['158994158@qq.com','nizhao2@lenovo.com','wangjin24@lenovo.com','pengch2@lenovo.com']  
                        to_addr.append(mailaddress) 
                    
                        # 构建message
                        msg = MIMEMultipart()              

                        msg['From'] = Header(from_addr)
                        msg['To'] = Header(",".join(to_addr))  
                        header_name ='考试合格通知： '+ student
                        msg['Subject'] = Header(header_name,'utf-8') #utf-8可以不加


                        # 以html格式构建邮件内容
                        send_str = '<html><body>'
                        send_str += '<left>' +'<font size="4" color="red">'+'  祝贺你通过考试！'+'</font>' +'</left>'      
                        send_str += '<center> </center>'  
                        send_str += '<left>'+'*姓名：'+student+'</left>'          
                        send_str += '<center> </center>'
                        send_str += '<left>'+'*考试科目：'+subject+'</left>'          
                        send_str += '<center> </center>'
                        send_str += '<left>'+ '*考试得分: '+'<font size="4" color="red">'+ score +'</font>'+'</left>'
                        send_str += '<center> </center>'   
                        send_str += '<left>'+ '*考题数量: '+ question_number +'</left>'
                        send_str += '<center> </center>'   
                        send_str += '<left>'+ '*错误数量: '+ fail_number +'</left>'
                        send_str += '<center> </center>'   
                        send_str += '<left>'+ '*答题时长（秒）: '+ duration +'</left>'                 
                        send_str += '<center> <br></center>'  
                        send_str += '<left>'+'<font size="3" color="blue">'+ '--------答错的题目及正确选项：------------ '+ '</font>'+'</left>'
                        send_str += '<center> <br></center>'  
                        for i in fail_questions:
                            send_str += '<left>'+ i +'</left>'
                            send_str += '<center> </center>'   



                        send_str += '<left>'+ ''+ '</left>' 
                        send_str += '<center> <br></center>'
                        send_str += '<left>' +'<font size="3" color="blue">'+'  后续步骤: 对于有实操要求的科目请进行实操确认和考核！'+'</font>' +'</left>'       

                        send_str += '</body></html>'

                        # 添加邮件内容
                        content = MIMEText(send_str, _subtype='html', _charset='utf8')
                        msg.attach(content)

                        # API 地址
                        api_url = 'http://100.64.5.171/WebServices/WS_EMAIL/WS_EMAIL.asmx?wsdl'
                        email_params = {
                            'message':  msg.as_string()  # 将邮件内容转换为字符串
                        }

                        # 发送邮件
                        try:
                            client = Client(api_url)
                            response = client.service.sendEmail(**email_params)
                            msg=("Email sent successfully:", response)
                        except Exception as e:
                            msg=("Failed to send email:", e)

                        print(msg)  


                    def jiaojuan():   

                        global signal   #设置倒计时标志位
                        signal=0            

                        time_end = datetime.datetime.now() #程序计时终点
                        totoal_t = (time_end - time_start).seconds

                        #.建立mysql的连接
                        conn = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',
                                password='123456',port=3306,charset='utf8')
                        # 获得游标
                        cursor = conn.cursor()  

                        cursor.execute('''SELECT test_name,count(*),exam_sequence,test_score from examrecord where name=%s and exam_sequence=(SELECT MAX(exam_sequence) FROM examrecord WHERE  NAME=%s ) ''',(name,name))
                        testname=cursor.fetchone()
                        test_name=testname[0]
                        selected_qty=testname[1]
                        exam_sequence=testname[2]
                        test_score=testname[3]
                        # print(selected_qty)
                        cursor.execute('''SELECT SUM(question_score),count(*) from examrecord where answer=selected and name=%s and exam_sequence=(SELECT MAX(exam_sequence) FROM examrecord WHERE  NAME=%s ) ''',(name,name))
                        final_score=cursor.fetchone()
                        
                        if final_score[0] is not None:        
                            passqty=final_score[1]
                            print(passqty)
                            examscore=math.ceil(int(passqty)*100/int(selected_qty))
                        else:
                            examscore=0
                            passqty=0
                        
                        
                        if examscore>=90 and examscore!=100:
                            msg='祝贺通过考试！错题将邮件发送！'+'\n'+test_name+'考试成绩：'+str(examscore)
                            result='Pass'
                        elif examscore==100:
                            msg='祝贺你满分通过考试！'+'\n'+test_name+'考试成绩：'+str(examscore)
                            result='Pass'
                        else:
                            msg='考试未达标，还需继续努力！'+'\n'+test_name+' 考试成绩：'+str(examscore)
                            result='Fail'
                            


                        print('题目个数：',selected_qty,'正确数量：',passqty)
                        print('交卷时间：',time_end,'秒')   
                        print('考试时长：',totoal_t,'秒')   
                        
                        #先删除（如果有）当日相同实验名称，相同人，相同Sequence的记录 (20240311添加，在423行中的exam_sequence<=%s添加了等号似乎也能替代本句？NO，有等号会把当次考试改成过期，不对)
                        cursor.execute('''delete from examresult where test_name=%s and name=%s and exam_sequence=%s and date=%s''',(test_name,name,exam_sequence,todaycolumn))
                        conn.commit()
                        #考试结果记录写入数据库表格examresult
                        query='''insert into examresult(test_name,test_score,date,time, duration,name,exam_sequence,question_number,
                        pass_number,fail_number,final_score,result,computer)values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'''          
                        
                        date=todaycolumn  
                        time=str(time_end.time())[:8]
                        duration=str(totoal_t)
                        exam_sequence
                        question_number=str(selected_qty)
                        pass_number=passqty
                        fail_number=str(selected_qty-passqty)
                        final_score=str(examscore)   
                        computer= getpass.getuser()       
                

                        values =(test_name,test_score,date,time, duration,name,exam_sequence,question_number, pass_number,fail_number,final_score,result,computer)
                        
                        cursor.execute(query,values) 
                        conn.commit() 

                         
                        if result=='Pass':
                            #把重复考试的之前实验过期处理
                            cursor.execute('''update examresult set result='expired', expired_date=%s, expired_reason='Duplicated exam' where test_name=%s and name=%s and exam_sequence<%s and result='Pass' ''',(todaycolumn,test_name,name,exam_sequence))
                            conn.commit() 
                            #查询过期天数，计算expected_expire_date，并更新examresult
                            cursor.execute('''select expired_days from exam_name_score where test_name =%s ''',(test_name))
                            getdays=cursor.fetchone()[0]   

                            expired_date=(datetime.datetime.now()+datetime.timedelta(days=getdays)).strftime('%Y-%m-%d %H:%M:%S')  #x天之后
                
                            cursor.execute('''update examresult set expired_date=%s, expired_reason='expected_expire_date' where test_name=%s and name=%s and exam_sequence=%s and result='Pass' ''',(expired_date,test_name,name,exam_sequence))
                            conn.commit()                       

                        student=name
                        subject=test_name
                        score=final_score



                        if examscore>=90:  
                            fail_questions=[]
                            cursor.execute('''select question,select_a,select_b,select_c,select_d,answer,selected from examrecord where answer!=selected and exam_sequence=%s and name=%s''',(exam_sequence,name))
                            fail_items=cursor.fetchall()
                            #如果某道题没有选择，那i[6]应该是None, 所以该错题不会被选择不会发在邮件中，有机会确认再改
                            for i in fail_items:
                                if i[6]=='A':
                                    selected_item=i[1]
                                if i[6]=='B':
                                    selected_item=i[2]
                                if i[6]=='C':
                                    selected_item=i[3]
                                if i[6]=='D':
                                    selected_item=i[4]
                                if i[6]=='':
                                    selected_item=''                           
                                if i[5]=='A':
                                    correct_answer=i[1]
                                if i[5]=='B':
                                    correct_answer=i[2]
                                if i[5]=='C':
                                    correct_answer=i[3]
                                if i[5]=='D':
                                    correct_answer=i[4]

                                fail_questions.append('#.题目：'+i[0])
                                fail_questions.append('   -错误选项：'+selected_item)
                                fail_questions.append('   -正确选项：'+correct_answer)     
                            


                            auto_email1(student,subject,score,question_number,fail_number,duration,fail_questions)
                            # root.destroy()


                        tk.messagebox.showinfo(title='Hi,'+name, message=msg)
                        root.destroy()

                    def jiaojuan_confirm():
                        question = tk.messagebox.askyesno(title='确认', message='确定要交卷吗？')
                        if question:                    
                            jiaojuan()

                    def problem_report():
                        question = tk.messagebox.askyesno(title='问题题目上报', message='如果你认为本道题目出的有问题，让你产生了疑义，或者你觉得这道题出的有错误或有待改进，可以点击“是”进行上报！')
                        if question:    
                            reporter= name_input
                            # print('上报：',question_id,test_name,reporter)        
                            cursor.execute('''select * from problematic_questions where question_id=%s and reporter=%s''',(question_id,reporter))
                            repeated_report=cursor.fetchone()
                            if not repeated_report:
                                query = '''insert into problematic_questions (question_id,test_name,question,select_a,select_b,select_c,select_d,select_e,select_f, answer,datetime,reporter,report_time,report_details)
                                values(%s,%s,%s, %s, %s, %s, %s, %s,%s,%s,%s,%s,%s,%s)'''
                                cursor.execute('''select * from examcontent where id=%s''',question_id)
                                reported_question = cursor.fetchone()

                                test_name=reported_question[1]
                                question=reported_question[3]
                                select_a=reported_question[4]
                                select_b=reported_question[5]
                                select_c=reported_question[6]
                                select_d=reported_question[7]
                                select_e=''
                                select_f=''
                                answer=reported_question[10]
                                datetime=reported_question[12]
                                report_time= datetime.now().strftime('%Y-%m-%d %H:%M:%S') 
                                report_details=''

                                values =(question_id,test_name,question,select_a,select_b,select_c,select_d,select_e,select_f,answer,datetime,reporter,report_time,report_details)
                                cursor.execute(query,values) 
                                conn.commit() 
                            else:
                                tk.messagebox.showerror(title='Hi',message='*同一题目不需要重复报告！')


                    #初始主界面，选择考试科目，填入姓名
                    test_seletion = cbx_1.get()
                    name_input = e.get()       
                    user_name = getpass.getuser()     
                    if test_seletion=='' or name_input=='' or name_input not in chinesenamelist: 
                        tk.messagebox.showerror(title='Hi',message='*注意：请选择实验，填写正确的中文姓名之后才能开始考试！')
                    
                    else:
                        #从题库按条件选出一套题目
                        testname=test_seletion
                        name=name_input
                        #免于检查3天只考一次的人
                        namelist0=['李军','倪昭','朱爽宁','李鑫','李华']

                        #建立mysql的连接
                        conn = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',password='123456',port=3306,charset='utf8')
                        # 获得游标
                        cursor = conn.cursor()

                         
                        cursor.execute('''select id from examrecord where test_name=%s and name=%s and date>=%s''',(testname,name,three_days_ago))
                        record_feedback=cursor.fetchall()
                        
                        if record_feedback!=() and name not in namelist0:
                            
                            tk.messagebox.showerror(title='Hi', message='同一科目的考试间隔应大于3天！')
                            sys.exit()

 
                        cursor.execute('''SELECT count(*) FROM examcontent where test_name=%s''',testname)
                        testnumber=cursor.fetchone()[0]
                        #取出settings
                        cursor.execute('''SELECT * FROM settings''')
                        settings=cursor.fetchall()
                        testlist=[]
                        for i in settings:
                            testlist.append(i[1])
                        print(testlist)


                        if testname in testlist:
                            cursor.execute('''SELECT * FROM settings where exam_name=%s''',testname)
                            settinginfors=cursor.fetchone()
                            sampling=settinginfors[2]
                            seconds=settinginfors[3]
                            select_number=int(testnumber*float(sampling))  #Setting中定义的科目，按setting中的设置来确定题目数量
                        else:
                            cursor.execute('''SELECT * FROM settings where exam_name='all' ''')
                            settinginforsall=cursor.fetchone()
                            sampling=settinginforsall[2]
                            seconds=settinginforsall[3]              

                            #只针对All中的科目，根据考题总数量来计算抽题数量 select_number，题目大于15个时，按0.8选题，小于15时，按实际个数
                            if testnumber>=15:
                                select_number=int(testnumber*float(sampling))
                            else:
                                select_number=testnumber


                         #如果是工作时间18点后，或者是周日：考100%题目，每题时间30秒
                        if n_time > d_time1 or weekno==6:
                            select_number=testnumber
                            seconds=30


                        
                        if name=='李华':                            
                            select_number=testnumber  #全抽按100%抽题

       
                        global allSeconds
                        allSeconds=select_number*int(seconds) #秒数 
                        m = allSeconds // 60 #分钟

                        print('最终选择的题目数量及总时长：',select_number,allSeconds)

                        #随机抽题，数量为select_number
                        cursor.execute('''SELECT * FROM examcontent where test_name=%s ORDER BY RAND() LIMIT %s''',(testname,select_number))
                        selectedrows=cursor.fetchall()

                        msg=name_input+': 你选择的科目是：'+test_seletion+', 确认后开始考试！'+'\n'+ ' 请掌握好答题时间，时间到将自动交卷！！！'
                        question=tk.messagebox.askquestion(title='Hi',message=msg)

                        if question=='yes':
                            win.destroy()  #关闭初始窗口

                            #把抽出的这套题目写入examrecord表格,20250622增加examconten_id的写入
                            rows=len(selectedrows)  
                            query='''insert into examrecord(test_name,test_score,question,select_a,select_b,select_c,select_d,answer,question_score,date,name, exam_sequence,username,examcontent_id)values (%s,%s,%s, %s, %s, %s, %s, %s,%s,%s,%s,%s,%s,%s)'''


                            #确定该人本次考试的exam_sequence
                            cursor.execute('''select max(exam_sequence) from examrecord where name=%s''',name)
                            seq_number=cursor.fetchone()
                            if seq_number[0] is not None:
                                exam_sequence=seq_number[0]+1
                            else:
                                exam_sequence=1

                            for row in selectedrows:                               
                                examcontent_id = row[0]  #20250622增加examconten_id的写入
                                test_name = row[1]
                                test_score = row[2]
                                question = row[3]
                                select_a = row[4]
                                select_b = row[5]
                                select_c = row[6]
                                select_d = row[7]
                                answer = row[10]
                                question_score = row[11]
                                username=user_name
                              
                                # date_time=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') 


                                values =(test_name,test_score,question,select_a,select_b,select_c,select_d,answer,question_score,todaycolumn,name,exam_sequence,username,examcontent_id)
                                # 执行sql语句，把抽出的这套题目写入examrecord表格
                                cursor.execute(query,values) 
                                conn.commit() 

                            #从examrecord中取出刚刚添加的这套题目，准备进行显示
                            cursor.execute('''select * from examrecord where name=%s and exam_sequence=%s''',(name,exam_sequence))
                            selectedrows1=cursor.fetchall()

                            root = Tk()
                            root.title(testname+'单选题'+' '+name+'  题目数量:'+str(select_number)+'  总时长:'+str(m)+'分钟')
                            root.columnconfigure(0, minsize=290)  # 设置grid布局0列最小宽度
                            root.columnconfigure(1, minsize=290)  # 设置grid布局1列最小宽度
                            root.resizable(False, False)  # 禁止手动调节窗体尺寸
                            root.geometry('+266+244') # 设置窗体的位置
                            root.minsize(850,320) # 设置窗体宽与高的最小尺寸,这是无图片时窗体尺寸
                            # root.minsize(555,270) # 设置窗体宽与高的最小尺寸,这是无图片时窗体尺寸



                            global number,db_id,question_id
                            number=0
                            db_id=selectedrows1[number][0]
                            question=selectedrows1[number][3]
                            selectA=selectedrows1[number][4]
                            selectB=selectedrows1[number][5]
                            selectC=selectedrows1[number][6]
                            selectD=selectedrows1[number][7]
                            answer=selectedrows1[number][10]
                            question_id=selectedrows1[number][17]  #为了上报问题
                            
               
                          

                            if selectC=='':
                                selectC='----'
                            if selectD=='':
                                selectD='----'                

                        
                            # Label用来显示题目
                            id = str(number+1)+".　"  # 题目序号  
                            la1 = Label(root, text=id+question, justify="left", height=2,
                                        anchor=W, wraplength=1200,font=('黑体', 12))  # 题目，靠左，多行左对齐，像素500换行
                            la1.grid(padx=10, pady=2, sticky=W+E, columnspan=2)
                            

                            #选择按钮  Radiobutton
                            var = IntVar()
                            var.set(0)
                            
                            ra1 = Radiobutton(root, text='A.' + selectA, variable=var,
                                            value=1, justify="left", wraplength=1200,font=('黑体', 12), command=save_selection)  # 选项1，靠左，多行左对齐，460像素换行
                            ra1.grid(padx=10, sticky=W, columnspan=2)
                            
                            ra2 = Radiobutton(root, text='B.' + selectB, variable=var,
                                            value=2, justify="left", wraplength=1200, font=('黑体', 12),command=save_selection)  # 选项2，靠左，多行左对齐，460像素换行
                            ra2.grid(padx=10, sticky=W, columnspan=2)
                            
                            
                            ra3 = Radiobutton(root, text='C.' + selectC, variable=var,
                                            value=3, justify="left", wraplength=1200, font=('黑体', 12),command=save_selection)  # 选项3，靠左，多行左对齐，460像素换行
                        
                            ra3.grid(padx=10, sticky=W, columnspan=2)
                        
                            ra4 = Radiobutton(root, text='D.' + selectD, variable=var,
                                            value=4, justify="left",wraplength=1200, font=('黑体', 12),command=save_selection)  # 选项4，靠左，多行左对齐，460像素换行
                            
                            ra4.grid(padx=10, sticky=W, columnspan=2)
                            
                            but1 = Button(root, text=" 上一题 ", width=8,height=1,font=('黑体', 12),command=previous)
                            but1.grid(row=6, column=0, sticky=E, padx=10,pady=10)  # 7行1列，靠右，上下间距10像素
                            but2 = Button(root, text=" 下一题 ", width=8,height=1,font=('黑体', 12),command=next)
                            but2.grid(row=6, column=1, sticky=W, padx=10, pady=10)  # 7行2列，靠左，上下间距10像素

                            but3 = Button(root, text=" 交卷 ", width=8,height=1,font=('黑体', 13),command=jiaojuan_confirm)
                            but3.grid(row=6, column=2, sticky=E, padx=30, pady=10)  # 7行3列，靠左，上下间距10像素

                            but3 = Button(root, text=" 上报 ", width=8,height=1,font=('黑体', 13),command=problem_report)
                            but3.grid(row=6, column=3, sticky=E, padx=50, pady=10)  # 7行3列，靠左，上下间距10像素

                            label = Label(root,text="00:00",height=2,font=("微软雅黑",14)) #分钟:秒
                            label.grid(row=6, column=0, sticky=W, padx=100, pady=10)   
                           
                           #-以下为20250623新增按键气球提示
                            balloon=Label(root)    #先定义一个显示气球                            

                            def Balloon_show(event,msg,fg=None,bg=None):#前景色和背景色可有可无，往后可以增加字体自定义
                                try:
                                    balloon.grid_forget()
                                except:
                                    pass#如果有存在的气泡提示框，那么隐藏，但不销毁
                                balloon['fg']=fg    
                                balloon['bg']=bg    
                                balloon['text']=msg    
                                balloon.grid(row=7,column=3,sticky='w',columnspan=4)     


                            def Balloon_destroy(event):
                                try:
                                    balloon.grid_forget()  #不能用place_forget(),只能用同一种
                                except:
                                    pass
                            
                            #使用bind时必须把相应控件布局放在bind之后
                            but3.bind('<Enter>',lambda event:Balloon_show(event,msg='=> 如认为本考题有问题，可上报！',fg='red',bg='Yellow')) 
                            but3.bind('<Leave>',Balloon_destroy)





                            global time_start
                            time_start = datetime.datetime.now() #程序计时起点

                            global signal #设置倒计时标志位
                            signal=1
                            def show():
                                global allSeconds
                                global id

                                allSeconds -= 1
                                m = allSeconds // 60  # 分钟
                                sec = allSeconds % 60 # 秒
                                label['text'] = "%02d:%02d"%(m,sec)
                                label['fg'] ='yellow'
                                if m==0:
                                    label['fg'] ='red'
                                    label['font']=("微软雅黑",14)
                                    pt=pts.init()
                                    if sec==59:
                                        pt.say('答题时间还有一分钟')
                                        pt.runAndWait()
                                if allSeconds==0:
                                    root.after_cancel(id)
                                    if signal==1:  #交卷后置0，不再提示                             
                                        pt=pts.init()
                                        pt.say('考试结束时间到了')
                                        pt.runAndWait()     
                                        jiaojuan()
                                        tk.messagebox.showwarning('提示',"时间到！自动交卷！")   

                                        #20240224, 因发现examresult中有重复的同一ID的考试记录，尝试性地把jiaojuan 移动到了tk.message之上                   
                                        
                            
                                    
                                else:
                                    id = root.after(1000,show)  

                            id = root.after(1000,show)  

                            root.mainloop()
                        else:
                            win.mainloop()


                else:
                    tk.messagebox.showwarning('提示',"考试需要使用专用电脑！") 

            else:
                tk.messagebox.showwarning('提示',"验证码错误！") 
                sys.exit()


 
 
        d_time = datetime.datetime.strptime(str(datetime.datetime.now().date()) + '9:00', '%Y-%m-%d%H:%M')
        d_time1 = datetime.datetime.strptime(str(datetime.datetime.now().date()) + '18:00', '%Y-%m-%d%H:%M')
        # 当前时间
        n_time = datetime.datetime.now()
        #判断当前日期是周几？
        weekno = datetime.datetime.today().weekday()
        #如果是工作时间9~18点，且不是周日
        if n_time > d_time and n_time < d_time1 and weekno!=6:
            var = tk.StringVar()
            entry_days=tk.Entry(win,textvariable=var)
            #建立mysql的连接  #获取setting中的密码
            conn = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',password='123456',port=3306,charset='utf8')
            # 获得游标
            cursor = conn.cursor()
            cursor.execute('''SELECT * FROM settings where exam_name='all' ''')
            settinginforsall=cursor.fetchone()      
            keys=settinginforsall[5] 
            var.set(keys)  
            select_and_display()
        else: #非工作时间或周日
            window_check_days=tk.Toplevel(win)
            window_check_days.geometry('400x160')
            window_check_days.title('管理员验证')

            days_enter=tk.StringVar()
            tk.Label(window_check_days,text='* 本次考试需要管理员验证！！！。',bg='yellow',font=('Arial',8)).place(x=10,y=10)
            tk.Label(window_check_days,text='请输入验证码:').place(x=10,y=60)               
            entry_days=tk.Entry(window_check_days,textvariable=days_enter,show='*')
            entry_days.place(x=130,y=60)  
            btn_confirm_days=tk.Button(window_check_days,text='确定',width=8,height=1, command=select_and_display)
            btn_confirm_days.place(x=150,y=115)



    def result_export( ):
        tk.messagebox.showwarning('提示',"导出需要约10~15秒,确定后开始执行!") 
        #定义前1个月内,前1年
        two_month_ago=(datetime.datetime.now()+datetime.timedelta(days=-60)).strftime('%Y-%m-%d') 
        one_year_ago=(datetime.datetime.now()+datetime.timedelta(days=-365)).strftime('%Y-%m-%d') 
        one_month_ago=(datetime.datetime.now()+datetime.timedelta(days=-500)).strftime('%Y-%m-%d')  #one_month_ago暂时改成500天
        one_month_later=(datetime.datetime.now()+datetime.timedelta(days=30)).strftime('%Y-%m-%d') 
 
        conn2 = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',
                password='123456',port=3306,charset='utf8')
        # 获得游标
        cursor2 = conn2.cursor()

        print('connected!')

        #20230920: 满一年的考试Pass记录更新状态为过期. 20250916取消，不需要了
        # cursor2.execute('''update examresult set result='expired', expired_date=%s, expired_reason='Over 1 year' where result='Pass' and date<%s ''',(todaycolumn,one_year_ago))
        # conn2.commit() 
        #20250916把满足过期条件的，pass改成expired
        cursor2.execute('''update examresult set result='expired', expired_reason='expired' where expired_date<=%s ''',(todaycolumn))
        conn2.commit() 
        #20250916: auto_assign expired_date
        cursor2.execute('''UPDATE examresult 
            INNER JOIN exam_name_score 
            ON examresult.test_id = exam_name_score.id 
            SET 
            examresult.expired_date = DATE_ADD(examresult.date, INTERVAL exam_name_score.expired_days DAY),
            examresult.expired_reason = 'export_update_on_expried_date' 
            WHERE 
            examresult.result='Pass'
            AND examresult.expired_date IS NULL 
            AND examresult.date > '2024-01-01'
            AND examresult.date < '2025-09-17'
          ''')
        conn2.commit() 
       #20250315新增：把examresult中的最后一列实验id进行更新
        cursor2.execute('''UPDATE examresult AS t SET test_id=(SELECT id FROM exam_name_score AS e WHERE e.test_name= t.test_name)''')
        conn2.commit()

        
       #查询并更新tcr_exam_record表格中的complete_status 列，超过限期1个月的就不再更新
        cursor2.execute('''select * from tcr_exam_record where complete_status='No' and request_time>%s''',two_month_ago)
        rows=cursor2.fetchall()
        for i in rows:
            id=i[0]
            name=i[1]
            test_name=i[2]
            request_time=str(i[3])[:10]  #截取年月日部分
            # print(id,name,test_name,request_time)


            cursor2.execute('''select test_name,date,name,result from examresult where name=%s and test_name like %s and result='Pass' and date>=%s''',(name,('%'+test_name+'%'),request_time))
            item=cursor2.fetchone()
            # print(item)
            
            if item is not None:
                cursor2.execute('''update tcr_exam_record set complete_status='Yes' where id=%s ''',id)
                conn2.commit() 

        print('tcr_exam_record update connected!')

       # 以下进行examresult 中 oldcapability列更新，已经有测试能力的行写入Y
        cursor2.execute('''select id, test_name, name,result,oldcapability,test_id from examresult where result='Pass' ''')
        rows=cursor2.fetchall()
        #建立mysql的连接
        conn1 = pymysql.connect(host=ip_address,user='labuser',db='exceltodb',
                password='123456',port=3306,charset='utf8')
        # 获得游标
        cursor1 = conn1.cursor()
        exam_id_list=[]
        for i in rows:
            id=i[0]
            name=i[2]
            test_id=i[5]
            cursor1.execute('''select * FROM capability WHERE Employee_name=%s and test_nameid=%s ''',(name,test_id)) 
            returns=cursor1.fetchall()
            if returns:  #如果有返回值
                cursor2.execute('''update examresult set oldcapability='Y' where id=%s ''',id)
                conn2.commit()
            # else:
            #     print(name,test_id,returns)

        # 暂停点
        # print("按'回车键'或'空格键'继续......")        
        # while True: 
        #     char=ord(msvcrt.getch()) 
        #     if char==13 or char==32:         
        #         break

        #20250418下面语句用上面的替代，直接看实验id
        # for i in rows:
        #     id=i[0]
        #     test_name_full=i[1]
        #     if '&' in test_name_full:
        #         test_name=test_name_full[:test_name_full.find('&')]
        #     else:
        #         test_name=test_name_full

        #     name=i[2]


        #     capability_list=[]
        #     cursor1.execute('''select test_name FROM capability WHERE Employee_name=%s ''',name) 
        #     rows = cursor1.fetchall()
        #     for i in rows:
        #         capability_list.append(i[0])
        #     # print(capability_list)
        #     for item in capability_list:
        #         if test_name in item:                    
 
        #             cursor2.execute('''update examresult set oldcapability='Y' where id=%s ''',id)
        #             conn2.commit()
        #             break        
 
        cursor2.close()
        conn2.close()
        cursor1.close()
        conn1.close()        
        print('examresult-oldcapability column updated!')





        today=datetime.datetime.now().strftime('%m%d')

         #获取用户名
        global user_name
        user_name = getpass.getuser() 
        print('用户名：',user_name) 
        #待获取名单
        global namelist
        namelist=['lijun24','nizhao2','wangjin24','Lijun24','pengch2','lizh48'] #定义可以获取考试结果的电脑

        if user_name in namelist:   
            print('用户名验证通过！')
            

            #建立mysql的连接
            conn = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',
                    password='123456',port=3306,charset='utf8')    
            cursor = conn.cursor()

            operation_test_list=[]
            cursor.execute('''select test_name from examoperation ''')
            list=cursor.fetchall()
            for i in list:
                operation_test_list.append(i[0])
            # print(operation_test_list)


            pathfile = r'C:\自动分活儿\Exam Record Summary'+today+'.xlsx'
            workbook = vb.Workbook(pathfile)
            workbook.save(pathfile)
            print('excel file produced!')
            workbook=vb.load_workbook(pathfile)
            workbook.create_sheet('details')
            workbook.create_sheet('测试能力得分')
            workbook.create_sheet('待学员确认')
            workbook.create_sheet('待老师确认')
            workbook.create_sheet('科目分值及老师信息')
            workbook.create_sheet('TCR更新重考')
            workbook.create_sheet('待考试名单')
            workbook.remove(workbook['Sheet'])
            # worksheet1 = workbook.active
            workbook.save(pathfile)
            worksheet1=workbook['details']
            worksheet2=workbook['测试能力得分']
            worksheet3=workbook['待学员确认']
            worksheet4=workbook['待老师确认']
            worksheet5=workbook['科目分值及老师信息']
            worksheet6=workbook['TCR更新重考']
            worksheet7=workbook['待考试名单']

            fontstyle = vb.styles.Font(name='Times New Roman',bold=True,size=10)  #标题
            fontstyle1 = vb.styles.Font(name='Arial',bold=True,size=10)    #标题
            fontstyle2 = vb.styles.Font(name='Arial',size=10,color='0000FF')  #正文红色
            fontstyle3 = vb.styles.Font(name='Arial',size=10,color='DC143C')  #正文红色
            alignstyle = vb.styles.Alignment(horizontal='left',vertical='center')  #居中
            alignstyle1 = vb.styles.Alignment(horizontal='center',vertical='center')  #居中
            cellbackground= vb.styles.PatternFill(fill_type='lightGray') 

            worksheet1['A1'] = '考试科目'  
            worksheet1['B1'] = '科目分值'  
            worksheet1['C1'] = '考试日期'
            worksheet1['D1'] = '学员'
            worksheet1['E1'] = '编号'
            worksheet1['F1'] = '题目总数'
            worksheet1['G1'] = '正确'
            worksheet1['H1'] = '错误'
            worksheet1['I1'] = '考试得分'
            worksheet1['J1'] = '结果'
            worksheet1['K1'] = '地点'   
            worksheet1['L1'] = '老师确认' 
            worksheet1['M1'] = '老师确认时间' 
            worksheet1['N1'] = '学员确认' 
            worksheet1['O1'] = '学员确认时间' 
            worksheet1['P1'] = '实操关注项'
            worksheet1['Q1'] = '关注时间'  
            worksheet1['R1'] = 'ID' 
            worksheet1['S1'] = '已有测试能力'

            worksheet2['A1'] = '姓名'  
            worksheet2['B1'] = '考试总分'  
            worksheet2['C1'] = '实际测试能力分' 
            worksheet2['D1'] = '综合能力分' 
            worksheet2['E1'] = '绩效得分'

            worksheet3['A1'] = '考试科目'  
            worksheet3['B1'] = '考试日期'  
            worksheet3['C1'] = '姓名'
            worksheet3['D1'] = '考试结果'  
            worksheet3['E1'] = '学员确认'

            worksheet4['A1'] = '考试科目'  
            worksheet4['B1'] = '考试日期'  
            worksheet4['C1'] = '姓名'
            worksheet4['D1'] = '考试结果'  
            worksheet4['E1'] = '学员确认'
            worksheet4['F1'] = '老师确认'
            worksheet4['G1'] = '可选老师1'  
            worksheet4['H1'] = '可选老师2'
            worksheet4['I1'] = '可选老师3'  
            worksheet4['J1'] = '可选老师4'
            worksheet4['K1'] = '可选老师5'            

            worksheet5['A1'] = 'ID'  
            worksheet5['B1'] = '考试科目'  
            worksheet5['C1'] = '分值'
            worksheet5['D1'] = '指导老师1'  
            worksheet5['E1'] = '指导老师2'
            worksheet5['F1'] = '指导老师3'
            worksheet5['G1'] = '指导老师4'
            worksheet5['H1'] = '指导老师5'
            worksheet5['I1'] = '过期天数'


            worksheet6['A1'] = '姓名'  
            worksheet6['B1'] = '考试科目'  
            worksheet6['C1'] = '发起时间'
            worksheet6['D1'] = '目标完成时间'  
            worksheet6['E1'] = '完成状态'
        
            worksheet7['A1'] = '科目ID'  
            worksheet7['B1'] = '科目'  
            worksheet7['C1'] = '姓名'
            worksheet7['D1'] = '考试时间'  
            worksheet7['E1'] = '当前状态'
            worksheet7['F1'] = '过期时间'
            worksheet7['G1'] = '备注'


            cursor.execute('''select count(*) from examresult where name!='李军' and date>='2024-09-01' ''')
            b=cursor.fetchone()  
            cursor.execute('''select id,test_name,test_score,date,name,exam_sequence,question_number,pass_number,fail_number,final_score,
             result,computer,instructor_name,instructor_date,technician_confirm,technician_date,pending_item,provide_date,test_id,
             oldcapability from examresult where name!='李军' and date>='2024-09-01' ''')
            allrows=cursor.fetchall()  
           
            for row in range(2,b[0]+2):      
                worksheet1.cell(row,1,value = allrows[row-2][1]).alignment = alignstyle 
                worksheet1.cell(row,2,value = allrows[row-2][2]).alignment = alignstyle 
                worksheet1.cell(row,3,value = allrows[row-2][3]).alignment = alignstyle 
                worksheet1.cell(row,4,value = allrows[row-2][4]).alignment = alignstyle 
                worksheet1.cell(row,5,value = allrows[row-2][5]).alignment = alignstyle 
                worksheet1.cell(row,6,value = allrows[row-2][6]).alignment = alignstyle             
                worksheet1.cell(row,7,value = allrows[row-2][7]).alignment = alignstyle 
                worksheet1.cell(row,8,value = allrows[row-2][8]).alignment = alignstyle 
                worksheet1.cell(row,9,value = allrows[row-2][9]).alignment = alignstyle 
                worksheet1.cell(row,10,value = allrows[row-2][10]).alignment = alignstyle 
                if allrows[row-2][10]=='Pass':
                    worksheet1.cell(row,1).font = fontstyle2
                    worksheet1.cell(row,4).font = fontstyle2 
                    worksheet1.cell(row,9).font = fontstyle2   
                    worksheet1.cell(row,10).font = fontstyle2  
                worksheet1.cell(row,11,value = allrows[row-2][11]).alignment = alignstyle 

                worksheet1.cell(row,12,value = allrows[row-2][12]).alignment = alignstyle 
                worksheet1.cell(row,12).font = fontstyle2  
                worksheet1.cell(row,13,value = allrows[row-2][13]).alignment = alignstyle 
                worksheet1.cell(row,14,value = allrows[row-2][14]).alignment = alignstyle 
                worksheet1.cell(row,14).font = fontstyle2  
                worksheet1.cell(row,15,value = allrows[row-2][15]).alignment = alignstyle 
                worksheet1.cell(row,16,value = allrows[row-2][16]).alignment = alignstyle 
                worksheet1.cell(row,17,value = allrows[row-2][17]).alignment = alignstyle 
                worksheet1.cell(row,18,value = allrows[row-2][18]).alignment = alignstyle 
                worksheet1.cell(row,19,value = allrows[row-2][19]).alignment = alignstyle 
                if allrows[row-2][10]==-1:
                    worksheet1.cell(row,4).font = fontstyle3 
                    worksheet1.cell(row,11).font = fontstyle3 

 
            update_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') 
 
            source_db = pymysql.connect(host=ip_address,user='labuser',db='exceltodb',password='123456',port=3306,charset='utf8')
            dest_db = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',password='123456',port=3306,charset='utf8')  
            # 执行查询语句
            with source_db.cursor() as cursor:
                cursor.execute('SELECT * FROM capability')
                data = cursor.fetchall()
            # 将数据复制到目标数据库
            with dest_db.cursor() as cursor:  
                #实际是先删除目标数据表内容再复制表格内容进来，从老数据库到新数据库
                cursor.execute('delete from capability')         
                cursor.executemany('INSERT INTO capability VALUES (%s,%s,%s,%s,%s,%s,%s)', data)
            # 提交更改并关闭连接
            dest_db.commit()
            source_db.close()
            dest_db.close()


            #建立mysql的连接
            conn = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',
                    password='123456',port=3306,charset='utf8')    
            cursor = conn.cursor()
            
            #从examresult中读取考试数据及得分汇总，排序，写入excel，同时从Capability中获取和自动分活表格中一致的实际测试能力数据，写入Excel, 同时会基于两种测试能力做一个综合计算
            cursor.execute('''SELECT name,SUM(test_score) FROM examresult WHERE result='Pass' and name !='李军' and name !='冯志伟' 
            and name !='李金波' and name !='黄溶' and name !='陈义' and name !='王路' and name !='李泽辉' and name !='谭露' and name !='柳心怡' and name !='袁丹' 
            and name !='黎阿敏' and name !='朱启萌' and name !='董胜' and name !='王畅畅' and name !='陈书恒' and name !='王海轮' and name !='马恩泽'
             and name !='金鼎' and name !='唐琴' and date>'2024-05-01' GROUP BY NAME ORDER BY SUM(test_score) desc ''')
            allrows=cursor.fetchall()  
            count=len(allrows)
            for row in range(2,count+2):      

                #针对每个名字，获取考试合格有效且有实际能力的项目分值之和.  20250315新增 test_id>9000的补算一个0.7，在算考试分时算了0.3了已经
                cursor.execute('''SELECT SUM(test_score) FROM examresult WHERE result='Pass' and (oldcapability ='Y' or test_id>9000) and name=%s ''',allrows[row-2][0])
                cap_score=cursor.fetchone()
                if cap_score[0] is None:
                    capability_score=0
                else:
                    capability_score=cap_score[0]               

                # 考试得分前面已经汇总，直接乘以0.3.     
                overall_score=math.ceil(allrows[row-2][1]*0.3+capability_score*0.7)  #两种测试能力各占30%,70%，相加后向上取整数
                


                #按200分对应20分来算绩效分：
                if overall_score>=200:
                    performance_score=20
                elif overall_score<=50:
                    performance_score=5
                else:
                    performance_score=math.ceil(0.1*overall_score)

                #把要写入Excel的数据复制一份到tester_capability表格中。(是不断在后面插入)，然后再写excle表
                cursor.execute('''insert into tester_capability (name,exam_score,capability_score,overall_score,performance_score,datetime)values(%s,%s,%s,%s,%s,%s)''',(allrows[row-2][0],allrows[row-2][1],cap_score[0],overall_score,performance_score,update_time))
            conn.commit()

            cursor.execute('''SELECT NAME,exam_score,capability_score,overall_score,performance_score FROM tester_capability 
            WHERE DATETIME=(select MAX(DATETIME) FROM tester_capability)  and name!='倪昭' order BY performance_score DESC ''')       



            allrows=cursor.fetchall()  
            count=len(allrows)
            for row in range(2,count+2):      
                worksheet2.cell(row,1,value = allrows[row-2][0]).alignment = alignstyle 
                worksheet2.cell(row,2,value = allrows[row-2][1]).alignment = alignstyle1
                worksheet2.cell(row,3,value = allrows[row-2][2]).alignment = alignstyle1 
                worksheet2.cell(row,4,value = allrows[row-2][3]).alignment = alignstyle1
                worksheet2.cell(row,5,value = allrows[row-2][4]).alignment = alignstyle1

                if allrows[row-2][4]<6:
                    worksheet2.cell(row,1).font = fontstyle2
                    worksheet2.cell(row,5).font = fontstyle2
            #加一个备注说明：
            comments='说明：'
            comments1='1. 综合能力分=考试总分x30% + 实际测试能力总分X70%'
            comments2='2. 绩效得分：综合能力分200对应满分20分，依次类推'
            comments3='3. 考试合格科目的实际测试能力数据才会被统计计算(个别考试科目除外）。'
            worksheet2.cell(2,8,value = comments).font= fontstyle
            worksheet2.cell(3,8,value = comments1).alignment = alignstyle
            worksheet2.cell(4,8,value = comments2).alignment = alignstyle
            worksheet2.cell(5,8,value = comments3).alignment = alignstyle

 
            cursor.execute('''select test_name,date,name, result from examresult where result='Pass' and oldcapability is Null and NAME!='王锦' and NAME!='李军' and NAME!='倪昭' and NAME!='李紫华' and NAME!='彭彩虹' and NAME!='汪焕' and NAME!='陈凯' and NAME!='赵俊凯' and technician_confirm IS Null and date>='2025-04-01' ORDER BY name ''')
            allrows=cursor.fetchall()  
            count=len(allrows)
            for row in range(2,count+2):      
                worksheet3.cell(row,1,value = allrows[row-2][0]).alignment = alignstyle 
                worksheet3.cell(row,2,value = allrows[row-2][1]).alignment = alignstyle1
                worksheet3.cell(row,3,value = allrows[row-2][2]).alignment = alignstyle 
                worksheet3.cell(row,4,value = allrows[row-2][3]).alignment = alignstyle1
                if allrows[row-2][0] in operation_test_list:
                    worksheet3.cell(row,5,value = '待学员确认').alignment = alignstyle1
                    worksheet3.cell(row,5).font = fontstyle3
 
                else:
                    worksheet3.cell(row,5,value = '无实操').alignment = alignstyle1
 


 
            cursor.execute('''select test_name, DATE, NAME, result, technician_confirm from examresult where technician_confirm='Y' and oldcapability is Null and instructor_name is Null and date>='2025-04-01' ORDER BY test_name''')
            allrows=cursor.fetchall()  
        
            count=len(allrows)
            for row in range(2,count+2):      
                worksheet4.cell(row,1,value = allrows[row-2][0]).alignment = alignstyle 
                worksheet4.cell(row,2,value = allrows[row-2][1]).alignment = alignstyle1
                worksheet4.cell(row,3,value = allrows[row-2][2]).alignment = alignstyle 
                worksheet4.cell(row,4,value = allrows[row-2][3]).alignment = alignstyle1    
                worksheet4.cell(row,5,value = allrows[row-2][4]).alignment = alignstyle1             
                worksheet4.cell(row,6,value = '待老师确认').alignment = alignstyle1
                worksheet4.cell(row,6).font = fontstyle3
                
                cursor.execute('''select instructor1,instructor2,instructor3,instructor4,instructor5 from exam_name_score where test_name=%s''',allrows[row-2][0])
                instructors=cursor.fetchone()

                worksheet4.cell(row,7,value = instructors[0]).alignment = alignstyle 
                worksheet4.cell(row,8,value = instructors[1]).alignment = alignstyle
                worksheet4.cell(row,9,value = instructors[2]).alignment = alignstyle 
                worksheet4.cell(row,10,value = instructors[3]).alignment = alignstyle   
                worksheet4.cell(row,11,value = instructors[4]).alignment = alignstyle                  



 
            cursor.execute('''select id,test_name, test_score, instructor1, instructor2, instructor3, instructor4, instructor5,expired_days from exam_name_score order by test_name''')
            allrows=cursor.fetchall()  
            count=len(allrows)
            for row in range(2,count+2):      
                worksheet5.cell(row,1,value = allrows[row-2][0]).alignment = alignstyle 
                worksheet5.cell(row,2,value = allrows[row-2][1]).alignment = alignstyle1
                worksheet5.cell(row,3,value = allrows[row-2][2]).alignment = alignstyle 
                worksheet5.cell(row,4,value = allrows[row-2][3]).alignment = alignstyle1    
                worksheet5.cell(row,5,value = allrows[row-2][4]).alignment = alignstyle1             
                worksheet5.cell(row,6,value = allrows[row-2][5]).alignment = alignstyle1
                worksheet5.cell(row,7,value = allrows[row-2][6]).alignment = alignstyle1
                worksheet5.cell(row,8,value = allrows[row-2][7]).alignment = alignstyle1
                worksheet5.cell(row,9,value = allrows[row-2][8]).alignment = alignstyle1


 
            cursor.execute('''select * from tcr_exam_record''')
            allrows=cursor.fetchall()  
            count=len(allrows)
            for row in range(2,count+2):      
                worksheet6.cell(row,1,value = allrows[row-2][1]).alignment = alignstyle 
                worksheet6.cell(row,2,value = allrows[row-2][2]).alignment = alignstyle1
                worksheet6.cell(row,3,value = allrows[row-2][3]).alignment = alignstyle 
                worksheet6.cell(row,4,value = allrows[row-2][4]).alignment = alignstyle1    
                worksheet6.cell(row,5,value = allrows[row-2][5]).alignment = alignstyle1     
                if allrows[row-2][5]=='No':
                    worksheet6.cell(row,5).font = fontstyle3  
                else:      
                    worksheet6.cell(row,5).font = fontstyle2
                
            #20250622 新增：有实际能力但是待考试的人员及科目清单汇总
            conn1 = pymysql.connect(host=ip_address,user='labuser',db='exceltodb', password='123456',port=3306,charset='utf8')
            cursor1 = conn1.cursor()

            conn2=pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',password='123456',port=3306,charset='utf8')  
            cursor2 = conn2.cursor()

            #1.先拿到考试科目ID清单,按id逐一到capability表中获取有该测试能力的人名单
            row=2
            cursor2.execute("select id, test_name from exam_name_score ")
            testlistwithID=cursor2.fetchall()
            for item in testlistwithID:
                id=item[0]
                test_name=item[1]

                if id>9000:  #id>9000 直接去examresult拿最后一条pass，且过期时间+/-1个月的数据进行显示
                    cursor2.execute('''SELECT 
                        t1.test_id,
                        t1.test_name,
                        t1.name,
                        t1.date,
                        t1.result,
                        t1.expired_date,
                        t1.expired_reason,
                        t1.exam_sequence
                        FROM 
                        examresult t1
                        INNER JOIN (
                        -- 子查询：获取每个 (test_name, name) 组合的最大 exam_sequence
                        SELECT 
                            test_name,
                            name,
                            MAX(exam_sequence) AS max_seq
                        FROM 
                            examresult
                        -- 若需要按特定id筛选，可在此处添加 WHERE 条件
                            WHERE result='Pass' or result='expired' and test_id=%s

                        GROUP BY 
                            test_name,  -- 按test_name分组（与id一一对应）
                            name        -- 同一test_name下按name分组
                        ) t2 ON 
                        t1.test_name = t2.test_name 
                        AND t1.name = t2.name 
                        AND t1.exam_sequence = t2.max_seq WHERE test_id=%s and expired_date>%s AND expired_date<%s ''',(id,id,one_month_ago, one_month_later))
                    reminder_list1=cursor2.fetchall()
                    for i in reminder_list1:
                        worksheet7.cell(row,1,value = i[0]).alignment = alignstyle   
                        worksheet7.cell(row,2,value = i[1]).alignment = alignstyle1
                        worksheet7.cell(row,3,value = i[2]).alignment = alignstyle 
                        worksheet7.cell(row,4,value = i[3]).alignment = alignstyle1    
                        worksheet7.cell(row,5,value = i[4]).alignment = alignstyle1  
                        worksheet7.cell(row,6,value = i[5]).alignment = alignstyle1
                        worksheet7.cell(row,7,value = i[6]).alignment = alignstyle1 
                        if i[4]=='expired':
                            worksheet7.cell(row,5).font = fontstyle3  
                            worksheet7.cell(row,6).font = fontstyle3 
                        else:      
                            worksheet7.cell(row,5).font = fontstyle2
                            worksheet7.cell(row,6).font = fontstyle2 
                        row=row+1  
                else:
                    names_with_id=[]   #id<9000的科目，先去capability查询出有该id测试能力的人名单names_with_id
                    
                    cursor1.execute('''select test_nameid,employee_name from capability where test_nameid=%s''',id)
                    name_list=cursor1.fetchall()                                     
                
                    for i in name_list:
                        names_with_id.append(i[1])
                    # print(id,names_with_id)                            
                    
                    #再用科目id去examresult中查询，该科目考试pass的人名单reminderlist2记录中的
                    cursor2.execute('''SELECT 
                        t1.test_id,
                        t1.test_name,
                        t1.name,
                        t1.date,
                        t1.result,
                        t1.expired_date,
                        t1.expired_reason,
                        t1.exam_sequence
                        FROM 
                        examresult t1
                        INNER JOIN (
                        -- 子查询：获取每个 (test_name, name) 组合的最大 exam_sequence
                        SELECT 
                            test_name,
                            name,
                            MAX(exam_sequence) AS max_seq
                        FROM 
                            examresult
                        -- 若需要按特定id筛选，可在此处添加 WHERE 条件
                            WHERE result='Pass' or result='expired' and test_id=%s
                        GROUP BY 
                            test_name,  -- 按test_name分组（与id一一对应）
                            name        -- 同一test_name下按name分组
                        ) t2 ON 
                        t1.test_name = t2.test_name 
                        AND t1.name = t2.name 
                        AND t1.exam_sequence = t2.max_seq WHERE test_id=%s and expired_date>%s AND expired_date<%s ''',(id,id,one_month_ago, one_month_later))
                    reminder_list2=cursor2.fetchall()
                    for i in reminder_list2:
                        if i[2] in names_with_id:
                       
                            worksheet7.cell(row,1,value = i[0]).alignment = alignstyle   
                            worksheet7.cell(row,2,value = i[1]).alignment = alignstyle1
                            worksheet7.cell(row,3,value = i[2]).alignment = alignstyle 
                            worksheet7.cell(row,4,value = i[3]).alignment = alignstyle1    
                            worksheet7.cell(row,5,value = i[4]).alignment = alignstyle1 
                            worksheet7.cell(row,6,value = i[5]).alignment = alignstyle1  
                            worksheet7.cell(row,7,value = i[6]).alignment = alignstyle1 
                            if i[4]=='expired':
                                worksheet7.cell(row,5).font = fontstyle3  
                                worksheet7.cell(row,6).font = fontstyle3 
                            else:      
                                worksheet7.cell(row,5).font = fontstyle2
                                worksheet7.cell(row,6).font = fontstyle2
                            row=row+1  

                    #下面看是否存在某人有测试能力但是没有pass考试记录的情况
                    for name in names_with_id:
                        cursor2.execute('''select test_id,test_name from examresult where (result='expired' or result ='Pass') and name=%s and test_id=%s''',(name,id))
                        name_list=cursor2.fetchall()   
                        if not name_list:
                            print("有测试能力，但未查到考试记录(expired or pass)：",id,test_name,name)

                        



  

            for cell in ['A1','B1','C1','D1','E1','F1','G1','H1','I1','J1','K1','L1','M1','N1','O1','P1','Q1','R1','S1']:    
                worksheet1[cell].fill = cellbackground
                worksheet1[cell].font = fontstyle1
                worksheet1[cell].alignment = alignstyle

                worksheet2[cell].fill = cellbackground
                worksheet2[cell].font = fontstyle1
                worksheet2[cell].alignment = alignstyle               

                worksheet3[cell].fill = cellbackground
                worksheet3[cell].font = fontstyle1
                worksheet3[cell].alignment = alignstyle1   

                worksheet4[cell].fill = cellbackground
                worksheet4[cell].font = fontstyle1
                worksheet4[cell].alignment = alignstyle1 

                worksheet5[cell].fill = cellbackground
                worksheet5[cell].font = fontstyle1
                worksheet5[cell].alignment = alignstyle1 
           
                worksheet6[cell].fill = cellbackground
                worksheet6[cell].font = fontstyle1
                worksheet6[cell].alignment = alignstyle1 

                worksheet7[cell].fill = cellbackground
                worksheet7[cell].font = fontstyle1
                worksheet7[cell].alignment = alignstyle1 


            worksheet1.column_dimensions['A'].width = 12
            worksheet1.column_dimensions['B'].width = 8
            worksheet1.column_dimensions['C'].width = 12
            worksheet1.column_dimensions['D'].width = 10
            worksheet1.column_dimensions['E'].width = 6
            worksheet1.column_dimensions['F'].width = 8
            worksheet1.column_dimensions['G'].width = 6
            worksheet1.column_dimensions['H'].width = 6
            worksheet1.column_dimensions['I'].width = 9
            worksheet1.column_dimensions['J'].width = 7
            worksheet1.column_dimensions['K'].width = 8

            worksheet1.column_dimensions['L'].width = 8
            worksheet1.column_dimensions['M'].width = 12
            worksheet1.column_dimensions['N'].width = 8
            worksheet1.column_dimensions['O'].width = 12
            worksheet1.column_dimensions['P'].width = 12
            worksheet1.column_dimensions['Q'].width = 12        
            worksheet1.column_dimensions['R'].width = 12
            worksheet1.row_dimensions[1].height =20
            worksheet1.auto_filter.ref = "A1:R1"
            
            worksheet2.column_dimensions['A'].width = 12
            worksheet2.column_dimensions['B'].width = 12
            worksheet2.column_dimensions['C'].width = 13
            worksheet2.column_dimensions['D'].width = 12
            worksheet2.column_dimensions['D'].width = 11

            worksheet3.column_dimensions['A'].width = 15
            worksheet3.column_dimensions['B'].width = 12
            worksheet3.column_dimensions['C'].width = 9
            worksheet3.column_dimensions['D'].width = 9
            worksheet3.column_dimensions['E'].width = 13


            worksheet4.column_dimensions['A'].width = 15
            worksheet4.column_dimensions['B'].width = 12
            worksheet4.column_dimensions['C'].width = 9
            worksheet4.column_dimensions['D'].width = 9
            worksheet4.column_dimensions['E'].width = 10
            worksheet4.column_dimensions['F'].width = 13

            worksheet5.column_dimensions['A'].width = 10
            worksheet5.column_dimensions['B'].width = 25
            worksheet5.column_dimensions['C'].width = 10
            worksheet5.column_dimensions['D'].width = 10
            worksheet5.column_dimensions['E'].width = 10
            worksheet5.column_dimensions['F'].width = 10
            worksheet5.column_dimensions['G'].width = 10
            worksheet5.column_dimensions['H'].width = 10
            worksheet5.column_dimensions['I'].width = 10

            worksheet6.column_dimensions['A'].width = 12
            worksheet6.column_dimensions['B'].width = 20
            worksheet6.column_dimensions['C'].width = 20
            worksheet6.column_dimensions['D'].width = 20
            worksheet6.column_dimensions['E'].width = 10
  
            worksheet7.column_dimensions['A'].width = 10
            worksheet7.column_dimensions['B'].width = 25
            worksheet7.column_dimensions['C'].width = 10
            worksheet7.column_dimensions['D'].width = 20
            worksheet7.column_dimensions['E'].width = 15
            worksheet7.column_dimensions['F'].width = 20
            worksheet7.column_dimensions['G'].width = 25            
            worksheet7.auto_filter.ref = "A1:E1"


            # 获取要隐藏的工作表（可以通过工作表名称或索引）
            sheet_to_hide = workbook['待学员确认']  # 通过名称获取
            # 隐藏工作表 - 普通隐藏
            sheet_to_hide.sheet_state = 'hidden'
            sheet_to_hide = workbook['待老师确认']  # 通过名称获取
            # 隐藏工作表 - 普通隐藏
            sheet_to_hide.sheet_state = 'hidden'
            sheet_to_hide = workbook['TCR更新重考']  # 通过名称获取
            # 隐藏工作表 - 普通隐藏
            sheet_to_hide.sheet_state = 'hidden'

            workbook.save(pathfile)  

            print('输出表格数据成功！')
            tkinter.messagebox.showinfo(title='Hi', message='导出考试数据成功！')
        else:
            tk.messagebox.showwarning('提示',"授权人员专用！") 

 
    def auto_email2(name,test_name,instructor):
        print(name,test_name,instructor)
        cursor.execute('''select mailbox from collection where name=%s''',name)
        mail_id=cursor.fetchone()
        mailaddress1=mail_id[0]+'@lenovo.com'
        cursor.execute('''select mailbox from collection where name=%s''',instructor)
        mail_id=cursor.fetchone()
        mailaddress2=mail_id[0]+'@lenovo.com'    

 
        from_addr='tcc_reliability@motorola.com'
        to_addr=['lijun24@lenovo.com','nizhao2@lenovo.com','wangjin24@lenovo.com','pengch2@lenovo.com','lizh48@lenovo.com']                       
        # to_addr=['158994158@qq.com','nizhao2@lenovo.com','wangjin24@lenovo.com','pengch2@lenovo.com']  
        to_addr.append(mailaddress1) 
        to_addr.append(mailaddress2) 
        # password='tolvjwntgghdbigd'  #for 1589

        # 构建message
        msg = MIMEMultipart()              

        msg['From'] = Header(from_addr)
        msg['To'] = Header(",".join(to_addr))  
        header_name ='测试能力更新通知： '+ name
        msg['Subject'] = Header(header_name,'utf-8') #utf-8可以不加


        # 以html格式构建邮件内容
        send_str = '<html><body>'
        send_str += '<left>' +'<font size="4" color="red">'+'  祝贺你已经拥有下面科目的测试能力！'+'</font>' +'</left>'      
        send_str += '<center> </center>'  
        send_str += '<left>'+'* 学员姓名：'+name+'</left>'          
        send_str += '<center> </center>'
        send_str += '<left>'+'* 考试科目：'+test_name+'</left>'          
        send_str += '<center> </center>'
        send_str += '<left>'+ '* 指导老师: '+ instructor +'</left>'
        send_str += '<center> </center>'   

        # send_str += '<left>' +'<font size="3" color="blue">'+'  后续步骤: 倪昭更新测试能力; 2. 进行实操考核; 3.指导老师确认实操考核合格后，你将拥有该科目的测试能力！'+'</font>' +'</left>'       

        send_str += '</body></html>'

        # 添加邮件内容
        content = MIMEText(send_str, _subtype='html', _charset='utf8')
        msg.attach(content)

        # API 地址
        api_url = 'http://100.64.5.171/WebServices/WS_EMAIL/WS_EMAIL.asmx?wsdl'
        email_params = {
            'message':  msg.as_string()  # 将邮件内容转换为字符串
        }

        # 发送邮件
        try:
            client = Client(api_url)
            response = client.service.sendEmail(**email_params)
            msg=("Email sent successfully:", response)
        except Exception as e:
            msg=("Failed to send email:", e)
        print(msg)  

    def auto_email3(name,test_name,instructor_name,m):
        print(name,test_name,instructor_name)
        cursor.execute('''select mailbox from collection where name=%s''',name)
        mail_id=cursor.fetchone()
        mailaddress=mail_id[0]+'@lenovo.com'
    

        # smtp_server='smtp.qq.com'
        from_addr='tcc_reliability@motorola.com'
        to_addr=['158994158@qq.com','lijun24@lenovo.com','nizhao2@lenovo.com']                       
        # to_addr=['158994158@qq.com','nizhao2@lenovo.com','wangjin24@lenovo.com','pengch2@lenovo.com']  
        to_addr.append(mailaddress) 
        # password='tolvjwntgghdbigd'  #for 1589

        # 构建message
        msg = MIMEMultipart()              

        msg['From'] = Header(from_addr)
        msg['To'] = Header(",".join(to_addr))  
        header_name ='实操关注项通知： '+ name
        msg['Subject'] = Header(header_name,'utf-8') #utf-8可以不加


        # 以html格式构建邮件内容
        send_str = '<html><body>'
        send_str += '<left>' +'<font size="4" color="red">'+'  实操关注项提醒：'+'</font>' +'</left>'      
        send_str += '<center> </center>'  
        send_str += '<left>'+'* 学员姓名：'+name+'</left>'          
        send_str += '<center> </center>'
        send_str += '<left>'+'* 考试科目：'+test_name+'</left>'          
        send_str += '<center> </center>'
        send_str += '<left>'+ '* 实操关注项: '+ m +'</left>'
        send_str += '<center> </center>'   
        send_str += '<left>'+ '* 指导老师: '+ instructor_name +'</left>'
        send_str += '<center> </center>' 

        # send_str += '<left>' +'<font size="3" color="blue">'+'  后续步骤: 倪昭更新测试能力; 2. 进行实操考核; 3.指导老师确认实操考核合格后，你将拥有该科目的测试能力！'+'</font>' +'</left>'       

        send_str += '</body></html>'

        # 添加邮件内容
        content = MIMEText(send_str, _subtype='html', _charset='utf8')
        msg.attach(content)

        # API 地址
        api_url = 'http://100.64.5.171/WebServices/WS_EMAIL/WS_EMAIL.asmx?wsdl'
        email_params = {
            'message':  msg.as_string()  # 将邮件内容转换为字符串
        }

        # 发送邮件
        try:
            client = Client(api_url)
            response = client.service.sendEmail(**email_params)
            msg=("Email sent successfully:", response)
        except Exception as e:
            msg=("Failed to send email:", e)
        print(msg)   
 
    def auto_email4(confirmed_name_list,test_selection,target_time):
         
        from_addr='tcc_reliability@motorola.com'
     
        to_addr=['lijun24@lenovo.com','nizhao2@lenovo.com','wangjin24@lenovo.com']
       
        for name in confirmed_name_list:
            cursor.execute('''select mailbox from collection where name=%s''',name)
            mail_id=cursor.fetchone()
            mailaddress=mail_id[0]+'@lenovo.com'    
            if mailaddress not in to_addr:      
                to_addr.append(mailaddress) 
           
        
        # 构建message
        msg = MIMEMultipart()              

        msg['From'] = Header(from_addr)
        msg['To'] = Header(",".join(to_addr))  
        header_name ='TCR更新后考试通知： '+ test_selection +'!'
        msg['Subject'] = Header(header_name,'utf-8') #utf-8可以不加


        # 以html格式构建邮件内容
        send_str = '<html><body>'
        send_str += '<left>' +'<font size="4" color="red">'+'  因TCR更新，请在七日内完成相关科目的考试，谢谢！'+'</font>' +'</left>'      
        send_str += '<center> </center>'  
        send_str += '<left>'+'* 考试科目：'+test_selection+'</left>'          
        send_str += '<center> </center>'
        send_str += '<left>'+'* 考试期限：'+target_time+'</left>'          
        send_str += '<center> </center>'


        send_str += '</body></html>'

        # 添加邮件内容
        content = MIMEText(send_str, _subtype='html', _charset='utf8')
        msg.attach(content)

        # API 地址
        api_url = 'http://100.64.5.171/WebServices/WS_EMAIL/WS_EMAIL.asmx?wsdl'
        email_params = {
            'message':  msg.as_string()  # 将邮件内容转换为字符串
        }

        # 发送邮件
        try:
            client = Client(api_url)
            response = client.service.sendEmail(**email_params)
            msg=("Email sent successfully:", response)
        except Exception as e:
            msg=("Failed to send email:", e)
        print(msg)  


    def confirmation():
        #.获取当前日期      
        todaycolumn=datetime.datetime.now().strftime('%Y-%m-%d')  

        #初始主界面，选择考试科目，填入姓名
        test_seletion = cbx_1.get()
        name_input = e.get()       
        user_name = getpass.getuser()     
        if test_seletion=='' or name_input=='': 
            tk.messagebox.showwarning(title='Hi',message='*注意：请选择实验，填写学员姓名之后才能进行实操确认！')
        
        else:
            testname=test_seletion
            name=name_input

            #建立mysql的连接
            conn = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',password='123456',port=3306,charset='utf8')
            # 获得游标
            cursor = conn.cursor()

 
            cursor.execute('select * from exam_name_score')
            teachers=cursor.fetchall()
            instructor_list=[]
            for i in teachers:
                if i[3] is not None and i[3] not in instructor_list:
                    instructor_list.append(i[3])
                if i[4] is not None and i[4] not in instructor_list:  
                    instructor_list.append(i[4])
                if i[5] is not None and i[5] not in instructor_list:  
                    instructor_list.append(i[5])
                if i[6] is not None and i[6] not in instructor_list:
                    instructor_list.append(i[6])
                if i[7] is not None and i[7] not in instructor_list:  
                    instructor_list.append(i[7])

            # print(instructor_list) 


 
            cursor.execute('''SELECT test_name,name, exam_sequence,result, instructor_name,technician_confirm,id FROM examresult where test_name=%s and NAME=%s and result='Pass' and exam_sequence=(SELECT MAX(exam_sequence) FROM examresult WHERE test_name=%s and NAME=%s and result='Pass') ''',(testname,name,testname,name))
            pass_result_query=cursor.fetchone()
           

            #如果没取出数据，说明考试没有合格
            if pass_result_query is None:
                tk.messagebox.showwarning(title='Hi',message='*注意：考试合格后才能进行实操确认！')
            else:
                print('YYY')
                test_name=pass_result_query[0]
                name=pass_result_query[1]
                instructor_name=pass_result_query[4]   #本查询无效，此时还没有老师姓名
                 
                technician_confirm=pass_result_query[5]
               
                id = pass_result_query[6]
                cursor.execute('''select * from examoperation where test_name=%s''',test_name)                
                item=cursor.fetchone()

                if item is None:
                    tk.messagebox.showwarning(title='Hi',message='*注意：该科目暂不需要进行实操确认！')

                else:
                    #如果学员已经确认，指导老师才能确认学员实操考核合格               
                    if technician_confirm=='Y' or technician_confirm is None:   #is None是20250916新增的，为的是学员没确认老师也能确认

                        def confirm_selection( ):
                            sumselections=var1.get()+var2.get()+var3.get()+var4.get()+var5.get()+var6.get()+var7.get()+var8.get()+var9.get()+var10.get()+var11.get()+var12.get()+var13.get()+var14.get()+var15.get()+var16.get()+var17.get()+var18.get()+var19.get()+var20.get()+var21.get()+var22.get()+var23.get()+var24.get()+var25.get()+var26.get()+var27.get()+var28.get()+var29.get()+var30.get()                      
                            print(m,sumselections)
                            if sumselections<m:
                                tk.messagebox.showerror(title='Hi',message='*注意：请选择所有选项后再确认！')
                                varr2.set(0)   



                        def teacher_confirm():
                            if varr2.get()==0:
                                tk.messagebox.showerror(title='Hi',message='*注意：请首先进行确认！')
                            elif varr2.get()==1 and e3.get()=='':
                                tk.messagebox.showerror(title='Hi',message='*注意：请输入指导老师姓名！')
                            elif varr2.get()==1 and e3.get() not in instructor_list:
                                tk.messagebox.showerror(title='Hi',message='*注意：经认证授权的指导老师才能确认！')
                            else:
                                instructor=e3.get()

                                #建立mysql的连接（20230614新增，确保稳定不出现没有反应的情况）
                                conn = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',password='123456',port=3306,charset='utf8')
                                # 获得游标
                                cursor = conn.cursor()

                                cursor.execute('''update examresult set instructor_name=%s,instructor_date=%s,instructor_location=%s where id=%s ''',(instructor,todaycolumn,user_name,id))
                                conn.commit()
                                #邮件通知
                                auto_email2(name,test_name,instructor)
                                root.destroy()                          


                
                        
                        # 少了这个就滚动不了
                        def myfunction(event):
                            canvas.configure(scrollregion=canvas.bbox("all"),width=800,height=400)
                        
                        
                        root=tk.Toplevel(win)
                        root.title('实操要点确认')
                        root.wm_geometry("1150x650")

                        lb0 = Label(root, text ="指导老师：实验操作要点确认："+test_name, font = "tahoma 16 normal",fg='blue')
                        lb0.place(x=200,y=0)       

                        canvas=Canvas(root)     # 创建画布
                        canvas.place(x=0,y=40,height=450,width=1140)


                        
                        myscrollbar=Scrollbar(root,orient="vertical",command=canvas.yview)      #创建滚动条
                        myscrollbar.place(x=1120,y=30,height=430)
                        canvas.configure(yscrollcommand=myscrollbar.set)
                        
                        
                        rollFrame=Frame(canvas,relief='groove',bd=2)     # 在画布上创建frame
                        canvas.create_window((0,0),window=rollFrame,anchor='nw')    # 要用create_window才能跟随画布滚动
                        rollFrame.bind("<Configure>",myfunction)
                        
                    # 数据库读取并显示内容：
                        m=0 #记录一下有内容的条数   
                        var1=tk.IntVar()
                        var2=tk.IntVar()  
                        var3=tk.IntVar()
                        var4=tk.IntVar()   
                        var5=tk.IntVar()
                        var6=tk.IntVar()  
                        var7=tk.IntVar()
                        var8=tk.IntVar()   
                        var9=tk.IntVar()
                        var10=tk.IntVar()  
                        var11=tk.IntVar()
                        var12=tk.IntVar()   
                        var13=tk.IntVar()
                        var14=tk.IntVar()  
                        var15=tk.IntVar()
                        var16=tk.IntVar()  
                        var17=tk.IntVar()
                        var18=tk.IntVar()  
                        var19=tk.IntVar()
                        var20=tk.IntVar()   
                        var21=tk.IntVar()
                        var22=tk.IntVar()  
                        var23=tk.IntVar()
                        var24=tk.IntVar()   
                        var25=tk.IntVar()
                        var26=tk.IntVar()  
                        var27=tk.IntVar()
                        var28=tk.IntVar()   
                        var29=tk.IntVar()
                        var30=tk.IntVar()  

                        varr2=tk.IntVar()
                        varr2.set(0)   #默认初始都不选择
                        r12=tk.Checkbutton(root,text='做为指导老师，我确认被考核人已掌握上述全部的实操要点！',fg='red',font=('times',12),variable=varr2,onvalue=1,offvalue=0,command=confirm_selection)
                        #点选时，A会被赋值给var,并执行command
                        r12.place(x=220,y=530)        
                        #create a Label
                        lb311 = ttk.Label(root, text = "学员姓名：", font = "tahoma 11 normal", foreground='yellow')
                        lb311.place(x=33,y=605)
                    
                        lb312 = ttk.Label(root, text = ' ', font = "tahoma 11 normal", foreground='yellow')
                        lb312.place(x=133,y=605)
                        lb312.config(text=name)
                        lb32 = ttk.Label(root, text = "指导老师姓名：", font = "tahoma 11 normal", foreground='yellow')
                        lb32.place(x=220,y=605)
                        e3=Entry(root,width = 9, show=None)  #show='*' 、show='1' 输什么都显示1
                        e3.place(x=365,y=605)


                        for i in range(1,31):
                            #exec('var%s=tk.IntVar()'%i)   #var1=tk.intvar, 依次类推. 例子： for i in range(10):  exec ("temp%s=%s"%(i,i*i))   print(temp5)
                            exec('''C%s = tk.Checkbutton(rollFrame, text =item[%s], variable = var%s,onvalue = 1, offvalue = 0, height=1,width = 200,bg='light grey',anchor=W)'''%(i,i+1,i))
                            if item[i+1]!='':
                                exec('C%s.grid(row=i,column=2)'%i)
                                m=m+1


                        btn_confirm=Button(root,text='确认',width=10,height=1,command=teacher_confirm,bg='#A0A0A0', fg='#0000FF' )
                        btn_confirm.place(x=480,y=603)
                        btn_exit=Button(root,text='退出',width=10,height=1,command=sw_exit,bg='#A0A0A0', fg='#0000FF' )
                        btn_exit.place(x=620,y=603)
                    
                    
                    
                        root.mainloop()


                    #学员尚未确认：显示学员确认界面：学员可以确认或者选择待培训项，只能二选一，一旦确认，不会再显示此界面，如果没确认，可以二选一
                    else:                      
                        def confirm_selection( ):
                            varr2.set(0)
                            sumselections=var1.get()+var2.get()+var3.get()+var4.get()+var5.get()+var6.get()+var7.get()+var8.get()+var9.get()+var10.get()+var11.get()+var12.get()+var13.get()+var14.get()+var15.get()+var16.get()+var17.get()+var18.get()+var19.get()+var20.get()+var21.get()+var22.get()+var23.get()+var24.get()+var25.get()+var26.get()+var27.get()+var28.get()+var29.get()+var30.get()                      
                            # print(m,sumselections)
                            if sumselections<m:
                                tk.messagebox.showerror(title='Hi',message='*注意：请选择所有选项后再确认！')
                                varr1.set(0)   
                        def training_selection( ):                        
                            varr1.set(0)
                            templist=['0'] 
                            global templist1
                            templist1=[] 
                            templist=['0',var1.get(),var2.get(),var3.get(),var4.get(),var5.get(),var6.get(),var7.get(),var8.get(),var9.get(),var10.get(),var11.get(),var12.get(),var13.get(),var14.get(),var15.get(),var16.get(),var17.get(),var18.get(),var19.get(),var20.get(),var21.get(),var22.get(),var23.get(),var24.get(),var25.get(),var26.get(),var27.get(),var28.get(),var29.get(),var30.get()]
                            for i in range(1,31):
                                if templist[i]==1:
                                    templist1.append(i)
                            # print(templist1)
                

                            #sumselections=var1.get()+var2.get()+var3.get()+var4.get()+var5.get()+var6.get()+var7.get()+var8.get()+var9.get()+var10.get()+var11.get()+var12.get()+var13.get()+var14.get()+var15.get()+var16.get()+var17.get()+var18.get()+var19.get()+var20.get()+var21.get()+var22.get()+var23.get()+var24.get()+var25.get()+var26.get()+var27.get()+var28.get()+var29.get()+var30.get()                      
                            # print(m,sumselections)
                            # if sumselections<m:
                            #     question=tk.messagebox.askquestion(title='Hi',message='*注意：请选择所有选项后再确认！')
                            #     varr1.set(0)  


                        def student_confirm():
                             #建立mysql的连接（20230614新增，确保稳定不出现没有反应的情况）
                            conn = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',password='123456',port=3306,charset='utf8')
                            # 获得游标
                            cursor = conn.cursor()                                   

                            if varr1.get()==0 and varr2.get()==0:
                                tk.messagebox.showerror(title='Hi',message='*注意：请首先进行选择和确认！')
                            elif (varr1.get()==1 and varr2.get()==0 and e3.get()=='') or (varr1.get()==0 and varr2.get()==1 and e3.get()==''):
                                tk.messagebox.showerror(title='Hi',message='*注意：请输入姓名！')
                            elif (varr1.get()==1 or varr2.get()==1) and e3.get()!=name:
                                tk.messagebox.showerror(title='Hi',message='*注意：学员姓名不正确！')
                            elif varr1.get()==1 and e3.get()==name:                           

                                cursor.execute('''update examresult set technician_confirm='Y',technician_date=%s,technician_location=%s where id=%s ''',(todaycolumn,user_name,id))
                                conn.commit()
                                tk.messagebox.showinfo(title='Hi',message='*注意：学员确认后请联系老师进行实操考核结果确认！')
                                root.destroy()    
                            else:
                                # print(templist1)
                                m=''
                                for i in range(0,len(templist1)):
                                    if i!=len(templist1)-1:
                                        m=m+str(templist1[i])+'/'
                                    else:
                                        m=m+str(templist1[i])
                                cursor.execute('''update examresult set pending_item =%s,provide_date=%s where id=%s ''',(m,todaycolumn,id))
                                conn.commit()
                                #邮件通知，实操关注项m
                                auto_email3(name,test_name,instructor_name,m)

                                tk.messagebox.showinfo(title='Hi',message='*注意：数据已保存！')
                                root.destroy()    

                            
                            varr1.set(0)
                            varr2.set(0)


                        
                        # 少了这个就滚动不了
                        def myfunction(event):
                            canvas.configure(scrollregion=canvas.bbox("all"),width=800,height=400)
                        
                        
                        root=tk.Toplevel(win)
                        root.title('实操要点确认：')
                        root.wm_geometry("1150x650")

                        lb0 = Label(root, text ="学员：实验操作要点确认："+test_name, font = "tahoma 16 normal",fg='blue')
                        lb0.place(x=220,y=0)       

                        canvas=Canvas(root)     # 创建画布
                        canvas.place(x=0,y=40,height=450,width=1140)


                        
                        myscrollbar=Scrollbar(root,orient="vertical",command=canvas.yview)      #创建滚动条
                        myscrollbar.place(x=1120,y=30,height=430)
                        canvas.configure(yscrollcommand=myscrollbar.set)
                        
                        
                        rollFrame=Frame(canvas,relief='groove',bd=2)     # 在画布上创建frame
                        canvas.create_window((0,0),window=rollFrame,anchor='nw')    # 要用create_window才能跟随画布滚动
                        rollFrame.bind("<Configure>",myfunction)
                        
                    # 数据库读取并显示内容：
                        m=0 #记录一下有内容的条数   
                        var1=tk.IntVar()
                        var2=tk.IntVar()  
                        var3=tk.IntVar()
                        var4=tk.IntVar()   
                        var5=tk.IntVar()
                        var6=tk.IntVar()  
                        var7=tk.IntVar()
                        var8=tk.IntVar()   
                        var9=tk.IntVar()
                        var10=tk.IntVar()  
                        var11=tk.IntVar()
                        var12=tk.IntVar()   
                        var13=tk.IntVar()
                        var14=tk.IntVar()  
                        var15=tk.IntVar()
                        var16=tk.IntVar()  
                        var17=tk.IntVar()
                        var18=tk.IntVar()  
                        var19=tk.IntVar()
                        var20=tk.IntVar()   
                        var21=tk.IntVar()
                        var22=tk.IntVar()  
                        var23=tk.IntVar()
                        var24=tk.IntVar()   
                        var25=tk.IntVar()
                        var26=tk.IntVar()  
                        var27=tk.IntVar()
                        var28=tk.IntVar()   
                        var29=tk.IntVar()
                        var30=tk.IntVar()  
                

 
                        varr1=tk.IntVar()
                        varr1.set(0)   #默认初始都不选择
                        varr2=tk.IntVar()
                        varr2.set(0)   #默认初始都不选择
                        #r11=tk.Radiobutton(root,text='做为学员，我确认已掌握上述全部的实操要点，满足正式上岗要求！',fg='red',font=('times',9),variable=varr1,value='A',command=confirm_selection)
                        r11=tk.Checkbutton(root,text='做为学员，我确认已掌握上述全部的实操要点！',font=('times',10),variable=varr1,onvalue=1,offvalue=0,command=confirm_selection)                   
                        #点选时，A会被赋值给var,并执行command
                        r11.place(x=220,y=505)
                        #r21=tk.Radiobutton(root,text='上面我点选的项目还需要安排进一步的实操培训！',font=('times',9),variable=varr1,value='B',command=confirm_selection)
                        r21=tk.Checkbutton(root,text='上面点选的项目还需安排进一步的实操培训！',font=('times',10),variable=varr2,onvalue=1,offvalue=0,command=training_selection) 
                        r21.place(x=220,y=540)        
                            

                    

                        lb311 = ttk.Label(root, text = "指导老师：", font = "tahoma 11 normal", foreground='yellow')
                        lb311.place(x=33,y=605)    
                        if instructor_name is None:
                            instructor_name='待确认'      
                        lb312 = ttk.Label(root, text = instructor_name, font = "tahoma 11 normal", foreground='yellow')
                        lb312.place(x=133,y=605)            

                        lb32 = ttk.Label(root, text = "学员姓名：", font = "tahoma 11 normal", foreground='yellow')
                        lb32.place(x=220,y=605)
                        e3=Entry(root,width = 12, show=None)  #show='*' 、show='1' 输什么都显示1
                        e3.place(x=320,y=605)



                        for i in range(1,31):
                            #exec('var%s=tk.IntVar()'%i)   #var1=tk.intvar, 依次类推. 例子： for i in range(10):  exec ("temp%s=%s"%(i,i*i))   print(temp5)
                            exec('''C%s = tk.Checkbutton(rollFrame, text =item[%s], variable = var%s,onvalue = 1, offvalue = 0, height=1,width = 200,bg='light grey',anchor=W)'''%(i,i+1,i))
                            if item[i+1]!='':
                                exec('C%s.grid(row=i,column=2)'%i)
                                m=m+1


                        btn_confirm=Button(root,text='确认',width=10,height=1,command=student_confirm,bg='#A0A0A0', fg='#0000FF' )
                        btn_confirm.place(x=475,y=601)
                        btn_exit=Button(root,text='退出',width=10,height=1,command=sw_exit,bg='#A0A0A0', fg='#0000FF' )
                        btn_exit.place(x=620,y=601)
                    
                    
                    
                        root.mainloop()

    def task_send():
        #.获取时间     
        todaycolumn=datetime.datetime.now().strftime('%Y-%m-%d') 
        request_time=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') 
        target_time=(datetime.datetime.now()+datetime.timedelta(days=7)).strftime('%Y-%m-%d %H:%M:%S')  #7天之后
        seven_days_ago=(datetime.datetime.now()+datetime.timedelta(days=-7)).strftime('%Y-%m-%d %H:%M:%S')  #7天之前
        expired_time=(datetime.datetime.now()+datetime.timedelta(days=30)).strftime('%Y-%m-%d')  #30天之后

        #获取选择的科目名称
        test_seletion = cbx_1.get()

        special_subjects=['','RTTP-1','Flipphone']  #特殊考试不需要发布
        # print(special_subjects)
         #获取用户名
        global user_name
        user_name = getpass.getuser()  
        #待获取名单
        global namelist
        namelist=['lijun24','nizhao2','wangjin24']   #定义可以进行发布的人

        if user_name in namelist and test_seletion not in special_subjects:   

             
            capability_name_list=['陈凯','汪焕','赵俊凯','熊春强','彭彩虹','张玉'] 
    
            conn = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',password='123456',port=3306,charset='utf8')
            cursor = conn.cursor()        
            cursor.execute('''select name from examresult where test_name=%s and result='Pass' ''',test_seletion)                           
            rows=cursor.fetchall()
            for i in rows:
                if i[0] not in capability_name_list:
                    capability_name_list.append(i[0])
            print('已经考试通过该的人：',capability_name_list)  #之前是90天，20231012去掉天数限制

            if '&' in test_seletion:
                test_name=test_seletion[:test_seletion.find('&')]
            else:
                test_name=test_seletion

            #建立mysql的连接
            conn = pymysql.connect(host=ip_address,user='labuser',db='exceltodb',
                    password='123456',port=3306,charset='utf8')
            # 获得游标
            cursor = conn.cursor()


            cursor.execute('''select Employee_name FROM capability WHERE test_name like %s ''',('%'+test_name+'%')) 
            rows = cursor.fetchall()
            for i in rows:
                if i[0] not in capability_name_list:
                    capability_name_list.append(i[0])
            print('又添加上分活儿表格中有测试能力的人：',capability_name_list)
            #定义一个经确认后的TCR发邮件名单            
            confirmed_name_list=[]

            def confirm_send(confirmed_name_list):
 
                conn = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',
                        password='123456',port=3306,charset='utf8')
                # 获得游标
                cursor = conn.cursor() 

                #将刚刚查询到的考试PASS的要发TCR考试提醒的人的考试PASS记录设为1月后过期（去掉之前90天的限制）,有些从capacity表格中来的人可能还没有考试过
                for name in confirmed_name_list:
                    cursor.execute('''update examresult set expired_date=%s, expired_reason='TCR update' where test_name=%s and name=%s and result='Pass' ''',(expired_time,test_seletion,name))
                    conn.commit()               



                #查询tcr_exam_record表格中是否有同名的测试，且日期在之前的7天内
                cursor.execute('''select test_name from tcr_exam_record where test_name=%s and request_time>%s group by test_name''',(test_name,seven_days_ago))
                items=cursor.fetchone()
                print(items)
                #如果搜不到相同测试名时执行，否则提示
                if items is None: 
                    query='''insert into tcr_exam_record(name,test_name,request_time,target_time,complete_status,exam_times)values(%s,%s,%s,%s,%s,%s)'''  
                                       
                    for name in confirmed_name_list:            
                     
                        complete_status='No'
                        exam_times=0

                        values=(name,test_name,request_time,target_time,complete_status,exam_times)
        
                        cursor.execute(query,values)     
                        conn.commit()        
                    
                    auto_email4(confirmed_name_list,test_seletion,target_time)

                else:
                    tk.messagebox.showwarning('提示',"同一考试科目7天内只能发送一次！")  


            def editwindow():
                windownew=tk.Toplevel(win)
                windownew.title('编辑与确认: 请保持原格式进行名单添加或删除，确认后按名单发送考试通知且之前考试PASS记录将失效！')
                windownew.wm_geometry("1500x100")

                initial_value=str(capability_name_list)
                entry_value=ttk.StringVar(value=initial_value)
                e=ttk.Entry(windownew,textvariable=entry_value,width=140)  
                e.pack()

                def name_confirmation():
                    var=e.get()
                    # print(var,type(var))
                    # 把字符串格式的列表转成真正的list  
                    global confirmed_name_list                  
                    confirmed_name_list = ast.literal_eval(var)   
                    print(confirmed_name_list,type(confirmed_name_list)) 
                    #确认姓名后关闭窗口，并跳转到前面的确认发送子程序
                    windownew.destroy()  
                 
                    confirm_send(confirmed_name_list)                 
     
 

                def close_window():
                    
                    windownew.destroy()
                

                # 下面这句定义窗口打X时的动作
                # windownew.protocol("WM_DELETE_WINDOW", name_confirmation)
                windownew.protocol("WM_DELETE_WINDOW", close_window)


                b1=tk.Button(windownew,text='确认',width=15,height=1,command=name_confirmation)
                b1.place(x=500,y=50)
                b2=tk.Button(windownew,text='取消',width=15,height=1,command=close_window)
                b2.place(x=700,y=50)              
   
                windownew.mainloop()               

            #弹出TCR发送前的编辑命名窗口
            editwindow()

      
        else:
            tk.messagebox.showwarning('提示',"授权人员及特定考试科目专用！") 


    def subject_delete():
        #获取用户名
        global user_name
        user_name = getpass.getuser()  
        #待获取名单
        global namelist
        namelist=['lijun24','nizhao2','wangjin24','zhangyu90','lizh48']   #定义可以进行发布的人
        if user_name in namelist:
            #获取选择的科目名称
            test_seletion = cbx_1.get()
            print(test_seletion)        
            #.建立mysql的连接
            conn = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',password='123456',port=3306,charset='utf8')
            # 获得游标
            cursor = conn.cursor()


            if test_seletion !='':
                question=tk.messagebox.askokcancel(title='考题与实操删除：'+test_seletion, message=test_seletion+ '所有考试及实操题目将被删除！！！') 
                if question:
                    cursor.execute('''delete from examcontent where test_name=%s''',test_seletion)                    
                    conn.commit() 
                    cursor.execute('''delete from examoperation where test_name=%s''',test_seletion)                    
                    conn.commit() 
                

                    tk.messagebox.showwarning('提示',"该科目考题/实操删除已完成！") 
      
                    # sys.exit() 


            else:
                tk.messagebox.showwarning('提示',"请先选择科目！") 
        else:
            tk.messagebox.showwarning('提示',"授权人员专用！") 


    def exam_plan_status_update():
        #获取用户名
        global user_name
        user_name = getpass.getuser()  
        #待获取名单
        global namelist
        namelist=['lijun24','nizhao2','wangjin24','zhangyu90']   #定义可以进行操作的人
        if user_name in namelist:
   
            #.建立mysql的连接
            conn = pymysql.connect(host=ip_address,user='labuser',db='tcclabrecord20220523',password='123456',port=3306,charset='utf8')
            # 获得游标
            cursor = conn.cursor()



            question=tk.messagebox.askokcancel(title='考试状态跟踪：', message=' 1.请将Exam Record Summary后的日期去掉;\n 2.将考试计划放到最后一个sheet以‘List’命名;') 
            if question:
                workbook = vb.load_workbook(r'C:\自动分活儿\Exam Record Summary.xlsx')
                worksheet = workbook['List']
                maxrows = worksheet.max_row -1
                print(maxrows)
                sheetzone = worksheet.iter_rows(min_row=2,max_row=maxrows,min_col=3,max_col=48)
                for row in sheetzone:
                    for cell in row:
                        if cell.value==1:
                            test_name=worksheet.cell(cell.row,1).value
                            technician_name=worksheet.cell(1,cell.column).value
                            print(test_name,technician_name)
                            cursor.execute('''SELECT instructor_name,technician_confirm,result FROM examresult WHERE test_name=%s AND NAME=%s AND result='Pass' ''',(test_name,technician_name))
                            infor=cursor.fetchone()
                            if infor is not None:
                                print(infor[0],infor[1],infor[2])
                                instructor_name=infor[0]
                                technician_confirm=infor[1]
                                result=infor[2]
                                fillcolor = PatternFill('solid', fgColor="FAEBD7")
                                fillcolor1 = PatternFill('solid', fgColor="00FFFF")
                                fillcolor2 = PatternFill('solid', fgColor="7FFF00")
                                
                                if result=='Pass':
                                    cell.fill = fillcolor
                                    workbook.save(r'C:\自动分活儿\Exam Record Summary.xlsx')
                                if technician_confirm=='Y':
                                    cell.fill = fillcolor1
                                    workbook.save(r'C:\自动分活儿\Exam Record Summary.xlsx')                        
                                if instructor_name is not None:
                                    cell.fill = fillcolor2
                                    workbook.save(r'C:\自动分活儿\Exam Record Summary.xlsx')




                tk.messagebox.showwarning('提示',"状态更新已完成，三种颜色分别代表考试已合格，学员已确认，老师已确认！") 
                sys.exit() 

        else:
            tk.messagebox.showwarning('提示',"授权人员专用！") 

    def help_me():  

    #   os.startfile(r'\\10.114.184.9\Tcc_Rel\04 Task_Assignment\备份\分活后备份\自动分活儿汇总20230321.pptx')
      
        help1='---------------------考试须知：---------------------\n'
        help2='1. 请不要携带手机来考试，不要在考试时和他人讨论。建议在9:00~18:00的工作时间进行考试。' 
        help3='2. 时间到自动交卷,也可提前交卷！'
        help4='3. 同一科目两次考试间隔大于2天！'
        help5='4. 考试合格后请自学实操，经指导老师确认后即拥有该项测试能力！'
        help6='5. 考试成绩占比30%，实际测试能力占比70%，二者相加后为绩效考核所用的综合测试能力分值。'
        # help7='#.明日在岗状态：正常工时8小时；歇班人员0小时；有加班意向的可以写10.5小时；夜班写0.1小时，自动分活不会包含夜班。如果想预留某个人不自动分派，预估工时至少写1小时或以上。'       
        # help8='#.2022/11/1更新：1. Test plan表格名称后面去掉2021; 2.'
        help=help1+'\n'+help2+'\n'+help3+'\n'+help4+'\n'+help5 +'\n'+help6
        tk.messagebox.showinfo(title='Help', message=help)
       

    def sw_exit():   
        cursor.close()
        sys.exit()   


    btn_query=ttk.Button(win,text='开始考试',command=start_exam)
    btn_query.place(x=80,y=170,width=100)
    btn_query=ttk.Button(win,text='指定考试',command=task_send)
    btn_query.place(x=80,y=215,width=100)
    btn_query=ttk.Button(win,text='实操确认',command=confirmation)
    btn_query.place(x=190,y=170,width=100) 
    btn_query=ttk.Button(win,text='删除考题',command=subject_delete)
    btn_query.place(x=190,y=215,width=100) 

    btn_query=ttk.Button(win,text='考试须知',command=help_me)
    btn_query.place(x=410,y=170,width=100) 
    btn_exit=ttk.Button(win,text='报告导出',command=result_export )
    btn_exit.place(x=300,y=170,width=100)
    btn_exit=ttk.Button(win,text='考试跟踪',command=exam_plan_status_update )
    btn_exit.place(x=300,y=215,width=100)

    btn_exit=ttk.Button(win,text='  退出  ',command=sw_exit )
    btn_exit.place(x=410,y=215,width=100)
    


    win.mainloop()




if __name__=="__main__":      
    version='V3.6'
    release_date='2025/09/20'
    # ip_address='localhost'
    ip_address='10.114.183.17'
    # ip_address='10.114.183.55'    
    windows()


# 关于考试过期：
# over_1_year export时的设置，取消了； TCR,保留，会把过期日设为1个月后，result暂时为pass, 日期到了会在export时设为过期，expired
# 重复考试马上过期，保留原状，duplicated
# 考试结束pass的马上设置过期日，原因设为expected_expire_date, 到期后会在export时把pass改为expired, 原因改为expired
# export时，会自动把所有过期日<=今天的设为过期：pass改为expired, 原因改为expired
# 有测试能力但是没有考试记录的人，程序输出界面单独打印出来，不再汇总在表格中